import  pymysql
import re
import datetime

def update_date(cursor,path,splitStr,dateType,tname):
    res=[]
    o_data=[]
    t_data=[]
    typeList=[]
    placeholder=''
    try:
        cursor.execute('DESC '+tname)
        typeList=cursor.fetchall()
        with open(path) as file:
            for row in file:
                o_data.append(row.strip().replace('"','').split(splitStr))
        for line in o_data:
            for i in range(len(typeList)):
                if re.match('^varchar',typeList[i][1])!=None:
                    continue
                if line[i]=='':
                    line[i]=None
                else:
                    if typeList[i][1]=='date':
                        line[i]=datetime.datetime.strptime(line[i],dateType)
                    elif typeList[i][1]=='int':
                        line[i]=int(line[i])
                    else: #re.match('^double',typeList[i][1])!=None:
                        line[i]=float(line[i])
            t_data.append(tuple(line))
        for i in range(len(typeList)):
            placeholder=placeholder+'%s,'
        sql='insert into '+tname+' values('+placeholder[:-1]+')'
        res.append(sql)
        res.append(t_data)
    except:
        print('表或加载文件不存在')
    return res
def conn_mysql(host='localhost', user = "root", passwd="123456", db="bigdata"):
    conn=[]
    try:
        connect = pymysql.connect(host='localhost', user = "root", passwd="123456", db="bigdata")
        cursor=connect.cursor()
        conn.append(connect)
        conn.append(cursor)
        return conn
    except:
        raise('连接数据库失败')


def insert_mysql(path,tname,dateType='%Y/%m/%d',splitStr=','):
    conn=conn_mysql()
    if len(conn)!=0:
        t_data=update_date(conn[1],path,splitStr,dateType,tname)
        if len(t_data)!=0:
            try:
                conn[1].executemany(t_data[0],t_data[1])
                conn[0].commit()
            except:
                conn[0].rollback()
    conn[0].close()
    conn[1].close()

insert_mysql('C:\\Users\\ASUS\\Desktop\\oracleTest\\endTest\\emp.txt','sm_emp')